-- get deprecated code with perfmon counters
DECLARE @CounterPrefix NVARCHAR(30);
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END;

SELECT * FROM sys.dm_os_performance_counters
WHERE [object_name] = @CounterPrefix + 'Deprecated Features'
AND cntr_value > 0;

-- extended event session to find deprecated code
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'DeprecationAnnouncement')
	DROP EVENT SESSION DeprecationAnnouncement ON SERVER;
GO
 
-- create event session
CREATE EVENT SESSION DeprecationAnnouncement ON SERVER 
ADD EVENT sqlserver.deprecation_announcement(
	ACTION 
	(
		sqlos.task_time,
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.client_pid,
		sqlserver.database_id,
		sqlserver.database_name,
		sqlserver.nt_username,
		sqlserver.session_id,
		sqlserver.sql_text)
	),
ADD EVENT sqlserver.deprecation_final_support(    
	ACTION (
		sqlos.task_time,
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.client_pid,
		sqlserver.database_id,
		sqlserver.database_name,
		sqlserver.nt_username,
		sqlserver.session_id,
		sqlserver.sql_text)
	)
ADD TARGET package0.event_file
	(SET FILENAME = N'C:\Temp\DeprecationAnnouncement.xel');
GO

-- enable event session
ALTER EVENT SESSION DeprecationAnnouncement ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION DeprecationAnnouncement ON SERVER STATE = STOP;
GO

-- query target data
SELECT
   event_data.value('(event[@name="deprecation_announcement"]/@timestamp)[1]', 'datetime') AS event_time,  
   event_data.value('(event/data[@name="feature"]/value)[1]', 'nvarchar(max)') AS feature,
   event_data.value('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(128)') AS [user_name],
   event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS client_app_name, 
   event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') AS client_hostname,
   event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') AS database_id,
   event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
   event_data.value('(event/data[@name="object_type"]/value)[1]', 'nvarchar(128)') AS object_type,
   event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(128)') AS [object_name],
   event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
	SELECT CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	(N'C:\Temp\DeprecationAnnouncement*.xel', NULL, NULL, NULL)
) AS tab;

-- cleanup
DROP EVENT SESSION DeprecationAnnouncement ON SERVER;
GO